-- =========================================================================
-- Title: Dynamic Audit Data Collecting - Create Queries table
-- Author: Nem W. Schlecht
-- Create Date: 2013-07-01
-- License: Creative Commons Attribution-Share Alike
-- Description: Dynamically generate data gathering script for SQL Auditng
--     system
-- =========================================================================

SET NOEXEC OFF;

USE ServerAudit;
GO

IF (
		@@SERVERNAME <> 'corpi-14967\dba' -- EDIT THIS!
		OR DB_NAME() <> 'ServerAudit'
		)
BEGIN
	PRINT 'You''re on the wrong server.';

	SET NOEXEC ON;
END;

IF OBJECT_ID('dbo.auditQueries', 'u') IS NOT NULL
BEGIN
	DROP TABLE [auditQueries];
END;
GO

CREATE TABLE [auditQueries] (
	queryId INT IDENTITY PRIMARY KEY
	, queryOrder INT NOT NULL
	, queryName VARCHAR(100) NOT NULL
	, query VARCHAR(max) NOT NULL
	, isActive BIT NOT NULL
	, createdOn DATETIME NOT NULL
	, createdBy VARCHAR(200) NOT NULL
	, modifiedOn DATETIME NOT NULL
	, modifiedBy VARCHAR(200) NOT NULL
	, notes TEXT NULL
	);

ALTER TABLE auditQueries ADD CONSTRAINT [aq_Active_def] DEFAULT(1)
FOR [isActive]
;

ALTER TABLE auditQueries ADD CONSTRAINT [aq_CO_def] DEFAULT(GETDATE())
FOR [createdOn]
;

ALTER TABLE auditQueries ADD CONSTRAINT [aq_CB_def] DEFAULT(SUSER_NAME())
FOR [createdBy]
;

ALTER TABLE auditQueries ADD CONSTRAINT [aq_MO_def] DEFAULT(GETDATE())
FOR [modifiedOn]
;

ALTER TABLE auditQueries ADD CONSTRAINT [aq_MB_def] DEFAULT(SUSER_NAME())
FOR [modifiedBy]
;
GO

SET NOCOUNT ON;

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	10
	, 'Pre-Script setup'
	, '
-- =========================================================================
-- Title: Data Gathering Script (Dynamically Generated)
-- Author: Nem W Schlecht
-- Create Date: 2014-11-18
-- Description: This scrip is dynamically generated based on SQL rules in
--     the ServerAudit database and the auditQueries table.  Please make
--     any necessary changes there.
-- =========================================================================
SET NOCOUNT ON;

'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	50
	, 'Create AuditTable varable'
	, '
DECLARE @AuditData TABLE (
	ikey VARCHAR(200) NOT NULL
	, ivalue VARCHAR(max) NULL
);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	100
	, 'Machine Name'
	, '
DECLARE @MachineName VARCHAR(100);
SET @MachineName = CAST(SERVERPROPERTY(''MachineName'') AS VARCHAR(100));
INSERT INTO @AuditData VALUES (''Machine Name'', @MachineName);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	200
	, 'Instance Name'
	, '
DECLARE @InstanceName VARCHAR(100);
SET @InstanceName = CAST(SERVERPROPERTY(''InstanceName'') AS VARCHAR(100));
INSERT INTO @AuditData VALUES (''Instance Name'', @InstanceName);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	300
	, 'Server Name'
	, '
DECLARE @ServerName VARCHAR(200);
SET @ServerName = CAST(SERVERPROPERTY(''ServerName'') AS VARCHAR(200));
INSERT INTO @AuditData VALUES (''Server Name'', @ServerName);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	400
	, 'Is Clustered?'
	, 
	'
DECLARE @IsClustered VARCHAR(5);
SET @IsClustered = CASE WHEN SERVERPROPERTY(''IsClustered'') = 0
			THEN ''no''
		WHEN SERVERPROPERTY(''IsClustered'') = 1
			THEN ''yes''
		ELSE ''ERROR''
		END
;
INSERT INTO @AuditData VALUES (''Clustered'', @IsClustered);
'
	);

--
-- Record all databases
INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	500
	, 'Database List'
	, '
INSERT INTO @AuditData
SELECT ''Database:CompatLevel:'' + name
	, compatibility_level
FROM sys.databases
;'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	600
	, 'Physical Server Name (NetBIOS)'
	, 
	'
DECLARE @ComputerNamePhysicalNetBIOS VARCHAR(100);
SET @ComputerNamePhysicalNetBIOS = CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS VARCHAR(100));
INSERT INTO @AuditData VALUES (''PhysicalNetBIOS'', @ComputerNamePhysicalNetBIOS);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	700
	, 'Machine Name'
	, 
	'
IF (@IsClustered = ''yes'')
BEGIN
	--
	-- Cluster Nodes
	-- PRINT ''-- Fetching cluster nodes''
	DECLARE @ClusterNodes VARCHAR(max);
	SELECT @ClusterNodes = STUFF(
				 (SELECT '','' + NodeName
				  FROM sys.dm_os_cluster_nodes
					ORDER BY NodeName
				  FOR XML PATH (''''))
				 , 1, 1, '''')
	;
	INSERT INTO @AuditData VALUES (''Cluster Nodes'', @ClusterNodes);
END;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	800
	, 'Server Edition'
	, '
DECLARE @ServerEdition VARCHAR(100);
SET @ServerEdition = CAST(SERVERPROPERTY(''Edition'') AS VARCHAR(100));
INSERT INTO @AuditData VALUES (''Server Edition'', @ServerEdition);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	900
	, 'Server Product Level'
	, 
	'
DECLARE @ServerProductLevel VARCHAR(100);
SET @ServerProductLevel = CAST(SERVERPROPERTY(''ProductLevel'') AS VARCHAR(100));
INSERT INTO @AuditData VALUES (''Server Product Level'', @ServerProductLevel);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1000
	, 'Server Produst Version'
	, 
	'
DECLARE @ServerProductVersion VARCHAR(100);
SET @ServerProductVersion = CAST(SERVERPROPERTY(''ProductVersion'') AS VARCHAR(100));
INSERT INTO @AuditData VALUES (''Server Product Version'', @ServerProductVersion);
'
	);

-- major.minor.build.revision
INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1100
	, 'Server Product Versions'
	, 
	'
DECLARE @ServerProductVersionMajor INT;
DECLARE @ServerProductVersionMinor INT;
DECLARE @ServerProductVersionBuild INT;
DECLARE @ServerProductVersionRevision INT;
SET @ServerProductVersionMajor = PARSENAME(@ServerProductVersion, 4);
SET @ServerProductVersionMinor = PARSENAME(@ServerProductVersion, 3);
SET @ServerProductVersionBuild = PARSENAME(@ServerProductVersion, 2);
SET @ServerProductVersionRevision = PARSENAME(@ServerProductVersion, 1);
INSERT INTO @AuditData VALUES (''Server Product Major Version'', @ServerProductVersionMajor);
INSERT INTO @AuditData VALUES (''Server Product Minor Version'', @ServerProductVersionMinor);
INSERT INTO @AuditData VALUES (''Server Product Build Version'', @ServerProductVersionBuild);
INSERT INTO @AuditData VALUES (''Server Product Revision Version'', @ServerProductVersionRevision);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1200
	, 'Server Collation'
	, '
DECLARE @ServerCollation VARCHAR(100);
SET @ServerCollation = CAST(SERVERPROPERTY(''Collation'') AS VARCHAR(100));
INSERT INTO @AuditData VALUES (''Server Collation'', @ServerCollation);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1300
	, 'Is FullText Installed?'
	, 
	'
DECLARE @IsFullTextInstalled VARCHAR(5);
SET @IsFullTextInstalled = CASE WHEN SERVERPROPERTY(''IsFullTextInstalled'') = 0
			THEN ''no''
		WHEN SERVERPROPERTY(''IsFullTextInstalled'') = 1
			THEN ''yes''
		ELSE ''ERROR''
		END
;
INSERT INTO @AuditData VALUES (''Full Text Installed'', @IsFullTextInstalled);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1400
	, 'Is Integrated Security Only?'
	, 
	'
DECLARE @IsIntegratedSecurityOnly VARCHAR(5);
SET @IsIntegratedSecurityOnly = CASE WHEN SERVERPROPERTY(''IsIntegratedSecurityOnly'') = 0
			THEN ''no''
		WHEN SERVERPROPERTY(''IsIntegratedSecurityOnly'') = 1
			THEN ''yes''
		ELSE ''ERROR''
		END
;
INSERT INTO @AuditData VALUES (''Integrated Security Only'', @IsIntegratedSecurityOnly);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1500
	, 'Current User'
	, '
DECLARE @CurrentUser VARCHAR(100);
SET @CurrentUser = SYSTEM_USER;
INSERT INTO @AuditData VALUES (''Current User'', @CurrentUser);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1600
	, 'Server Version String'
	, '
DECLARE @LongVersion VARCHAR(1000);
SET @LongVersion = @@VERSION;
INSERT INTO @AuditData VALUES (''Version String'', @LongVersion);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1700
	, 'Installation Date'
	, 
	'
DECLARE @InstallDate VARCHAR(20);
SELECT @InstallDate = CONVERT(VARCHAR(20), createdate, 120)
FROM sys.syslogins
WHERE [sid] = 0x010100000000000512000000
;
INSERT INTO @AuditData VALUES (''Install Date'', @InstallDate);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1800
	, 'Windows OS Information'
	, 
	'
IF (OBJECT_ID(''sys.dm_os_windows_info'') IS NOT NULL)
BEGIN
	DECLARE @WindowsRelease VARCHAR(100);
	DECLARE @WindowsServicePackLevel VARCHAR(100);
	DECLARE @WindowsSKU VARCHAR(100);
	DECLARE @WindowsLanguageVersion VARCHAR(100);
	
	SELECT @WindowsRelease = windows_release
		, @WindowsServicePackLevel = windows_service_pack_level
		, @WindowsSKU = windows_sku
		, @WindowsLanguageVersion = os_language_version
	FROM sys.dm_os_windows_info
	;
	
	INSERT INTO @AuditData VALUES (''Windows Release'', @WindowsRelease);
	INSERT INTO @AuditData VALUES (''Windows Service Pack Level'', @WindowsServicePackLevel);
	INSERT INTO @AuditData VALUES (''Windows SKU'', @WindowsSKU);
	INSERT INTO @AuditData VALUES (''Windows Language Version'', @WindowsLanguageVersion);
END;
ELSE
BEGIN
	INSERT INTO @AuditData VALUES (''Windows Release'', ''(Not Primary Instance)'');
	INSERT INTO @AuditData VALUES (''Windows Service Pack Level'', ''(Not Primary Instance)'');
	INSERT INTO @AuditData VALUES (''Windows SKU'', ''(Not Primary Instance)'');
	INSERT INTO @AuditData VALUES (''Windows Language Version'', ''(Not Primary Instance)'');
END;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1900
	, 'Server Hardware Information'
	, 
	'
DECLARE @ServerCPUCount VARCHAR(2);
DECLARE @ServerHyperThreadRadio VARCHAR(2);
DECLARE @ServerPhysicalCPUCount VARCHAR(2);
DECLARE @ServerPhysicalMemory VARCHAR(40);
SET @ServerPhysicalMemory = 0;

--
-- On 2012 SQL Servers, memory is stored in kb, not bytes, and
-- the field name is different
--
DECLARE @param NVARCHAR(500);
DECLARE @SQL_Hardware NVARCHAR(2000);

SET @param = ''
	@cpu NVARCHAR(1000) OUTPUT
	, @hyper NVARCHAR(1000) OUTPUT
	, @physcpu NVARCHAR(1000) OUTPUT
	, @physmem NVARCHAR(1000) OUTPUT
	'';

SET @SQL_Hardware = ''
	SELECT @cpu = cpu_count
		, @hyper = hyperthread_ratio
		, @physcpu = cpu_count/hyperthread_ratio
	'';
	
IF (CAST(@ServerProductVersionMajor AS INT) < 11)
BEGIN
	SET @SQL_Hardware = @SQL_Hardware
		+ '', @physmem = physical_memory_in_bytes/1048576
			FROM sys.dm_os_sys_info''
	;
END;
ELSE
BEGIN
	SET @SQL_Hardware = @SQL_Hardware
		+ '', @physmem = physical_memory_kb/1024
			FROM sys.dm_os_sys_info''
	;
END;

-- PRINT @SQL_Hardware;

EXECUTE sp_executesql
	@stmt = @SQL_Hardware
	, @params = @param
	, @cpu = @ServerCPUCount OUTPUT
	, @hyper = @ServerHyperThreadRadio OUTPUT
	, @physcpu = @ServerPhysicalCPUCount OUTPUT
	, @physmem = @ServerPhysicalMemory OUTPUT
;

INSERT INTO @AuditData VALUES (''Server CPU Count'', @ServerCPUCount);
INSERT INTO @AuditData VALUES (''Server HyperThread Ratio'', @ServerHyperThreadRadio);
INSERT INTO @AuditData VALUES (''Server Physical CPU Count'', @ServerPhysicalCPUCount);
INSERT INTO @AuditData VALUES (''Server Physical Memory MB'', @ServerPhysicalMemory);
INSERT INTO @AuditData VALUES (''Server Physical Memory GB'', CAST(ROUND(CAST(@ServerPhysicalMemory AS DECIMAL(10,2))/1024.0, 2) AS DECIMAL(10,2)));
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2100
	, 'Server Start/Up Time'
	, 
	'
DECLARE @ServerStartTime DATETIME;
DECLARE @ServerStartTimeText VARCHAR(30);

IF (CAST(@ServerProductVersionMajor AS INT) > 9)
BEGIN
	-- We have to do dynamic SQL, because on pre v10 SQL Servers,
	-- the sqlserver_start_time doesn''t exist and will cause a parse
	-- error.
	DECLARE @SQL_Start_Time NVARCHAR(2000);
	DECLARE @SSTnv NVARCHAR(100);
	DECLARE @SSTTnv NVARCHAR(100);

	SET @param = ''
		@sstt NVARCHAR(1000) OUTPUT
		, @sst NVARCHAR(1000) OUTPUT
		'';
	
	SET @SQL_Start_Time = ''
		SELECT @sstt = CONVERT(VARCHAR(20), sqlserver_start_time, 120)
			, @sst = sqlserver_start_time
		FROM sys.dm_os_sys_info
	'';
	EXECUTE sp_executesql
		@stmt = @SQL_Start_time
		, @params = @param
		, @sstt = @SSTTnv OUTPUT
		, @sst = @SSTnv OUTPUT
	;
	
	SET @ServerStartTime = @SSTnv;
	SET @ServerStartTimeText = @SSTTnv;
	INSERT INTO @AuditData VALUES (''SQL Server Start Time'', @ServerStartTimeText);
END;
ELSE
BEGIN
	-- Not as accurate fall-back method
	-- look at the create_date of tempdb
	SELECT @ServerStartTime = create_date
		, @ServerStartTimeText = CONVERT(VARCHAR(20), create_date, 120)
	FROM sys.databases
	WHERE NAME = ''tempdb''
	;

	INSERT INTO @AuditData VALUES (''SQL Server Start Time'', @ServerStartTimeText);
END;
	
DECLARE @Uptime VARCHAR(100);
DECLARE @UptimeTotalSecs INT;
--DECLARE @RemainSecs INT;
DECLARE @UptimeDays INT;
DECLARE @UptimeHours INT;
DECLARE @UptimeMins INT;
DECLARE @UptimeSecs INT;

SET @UptimeTotalSecs = DATEDIFF(s, @ServerStartTime, GETDATE());
--SELECT @UptimeTotalSecs;
SET @UptimeDays = @UptimeTotalSecs / (60 * 60 * 24);  -- days
--SELECT @UptimeDays AS ''Days''
SET @UptimeTotalSecs = @UptimeTotalSecs - (@UptimeDays * 60 * 60 * 24);
SET @UptimeHours = @UptimeTotalSecs / (60 * 60);
--SELECT @UptimeHours AS ''Hours''
SET @UptimeTotalSecs = @UptimeTotalSecs - (@UptimeHours * 60 * 60);
SET @UptimeMins = @UptimeTotalSecs / 60;
--SELECT @UptimeMins AS ''Mins''
SET @UptimeSecs = @UptimeTotalSecs - (@UptimeMins * 60);
--SELECT @UptimeSecs AS ''Secs''
SET @Uptime = CASE WHEN @UptimeDays > 0
				THEN CAST(@UptimeDays AS VARCHAR(20)) + '' days, ''
				ELSE ''''
				END
			+ CASE WHEN @UptimeHours > 0
				THEN CAST(@UptimeHours AS VARCHAR(20)) + '' hours, ''
				ELSE ''''
				END
			+ CASE WHEN @UptimeMins > 0
				THEN CAST(@UptimeMins AS VARCHAR(20)) + '' minutes, ''
				ELSE ''''
				END
			+ CAST(@UptimeSecs AS VARCHAR(20)) + '' seconds''
;
INSERT INTO @AuditData VALUES (''SQL Server Uptime'', @Uptime);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2200
	, 'Server Hardware Information'
	, 
	'
DECLARE @ServerManufacturer VARCHAR(100);
DECLARE @ServerModel VARCHAR(100);
DECLARE @LogRead TABLE (
	LogDate DATETIME NOT NULL
	, ProcessInfo VARCHAR(100) NULL
	, LogText VARCHAR(MAX) NULL
);
--
-- 2012 made this a standard system proc and not an experimental one
INSERT INTO @LogRead
EXEC sp_readerrorlog
	@p1 = 0
	, @p2 = 1
	, @p3 = ''Manufacturer''
;

DECLARE @SystemManufacturerLoc INT;
DECLARE @SystemModelLoc INT;
SELECT @SystemManufacturerLoc = CHARINDEX(''System Manufacturer: '', LogText)
	, @SystemModelLoc = CHARINDEX(''System Model: '', LogText)
	, @ServerManufacturer = SUBSTRING(LogText
										, @SystemManufacturerLoc + 22
										, @SystemModelLoc - (@SystemManufacturerLoc + 25)
										)
	, @ServerModel = SUBSTRING(LogText
								, @SystemModelLoc + 15
								, DATALENGTH(LogText) - (@SystemModelLoc + 16)
								)
FROM @LogRead
;

INSERT INTO @AuditData VALUES (''Server Manufacturer'', @ServerManufacturer);
INSERT INTO @AuditData VALUES (''Server Model'', @ServerModel);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	1900
	, 'Processor Information'
	, 
	'
DECLARE @ProcessorName VARCHAR(100);
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''HARDWARE\DESCRIPTION\System\CentralProcessor\0''
    , @value_name = ''ProcessorNameString''
    , @value = @ProcessorName OUTPUT
;
INSERT INTO @AuditData VALUES (''Processor Name'', @ProcessorName);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2300
	, 'Instance Sub-directory'
	, 
	'
DECLARE @instance_subdirectory VARCHAR(100);
DECLARE @instance_reg_path VARCHAR(200);
SET @instance_reg_path = ''SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'';
EXEC xp_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = @instance_reg_path
    , @value_name = @@SERVICENAME
    , @value = @instance_subdirectory OUTPUT
;
INSERT INTO @AuditData VALUES (''Instance Sub-directory'', @instance_subdirectory);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2400
	, 'Instance Software Root Path'
	, 
	'
DECLARE @instance_full_path VARCHAR(100);
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\Setup''
    , @value_name = ''SQLPath''
    , @value = @instance_full_path OUTPUT
;
INSERT INTO @AuditData VALUES (''Instance Path'', @instance_full_path);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2500
	, 'Default DATA Directory'
	, 
	'
DECLARE @default_data_dir VARCHAR(1000);
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\MSSQLServer''
    , @value_name = ''DefaultData''
    , @value = @default_data_dir OUTPUT
;

IF (@default_data_dir IS NULL)
BEGIN
    EXEC master.dbo.xp_instance_regread
		@rootkey = ''HKEY_LOCAL_MACHINE''
		, @key = ''Software\Microsoft\MSSQLServer\Setup''
		, @value_name = ''SQLDataRoot''
		, @value = @default_data_dir OUTPUT
		;
    SET @default_data_dir = @default_data_dir + ''\Data'';
END;
INSERT INTO @AuditData VALUES (''Default DATA Directory'', @default_data_dir);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2600
	, 'Default LOG Directory'
	, 
	'
DECLARE @default_log_dir VARCHAR(1000);
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\MSSQLServer''
    , @value_name = ''DefaultLog''
    , @value = @default_log_dir OUTPUT
;

IF (@default_log_dir IS NULL)
BEGIN
    EXEC master.dbo.xp_instance_regread
		@rootkey = ''HKEY_LOCAL_MACHINE''
		, @key = ''Software\Microsoft\MSSQLServer\Setup''
		, @value_name = ''SQLDataRoot''
		, @value = @default_log_dir OUTPUT
		;
    SET @default_log_dir = @default_log_dir + ''\Log'';
END;
INSERT INTO @AuditData VALUES (''Default LOG Directory'', @default_log_dir);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2700
	, 'Default BACKUP Directory'
	, 
	'
DECLARE @default_backup_dir VARCHAR(1000);
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\MSSQLServer''
    , @value_name = ''BackupDirectory''
    , @value = @default_backup_dir OUTPUT
;

IF (@default_backup_dir IS NULL)
BEGIN
    EXEC master.dbo.xp_instance_regread
		@rootkey = ''HKEY_LOCAL_MACHINE''
		, @key = ''Software\Microsoft\MSSQLServer\Setup''
		, @value_name = ''SQLDataRoot''
		, @value = @default_backup_dir OUTPUT
		;
    SET @default_backup_dir = @default_backup_dir + ''\Backup'';
END;
INSERT INTO @AuditData VALUES (''Default BACKUP Directory'', @default_backup_dir);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2800
	, 'Default ERROR Log Location'
	, 
	'
DECLARE @default_error_directory VARCHAR(1000);
EXEC master.dbo.xp_instance_regread
	@rootkey = ''HKEY_LOCAL_MACHINE''
	, @key = ''Software\Microsoft\MSSQLServer\CPE''
	, @value_name = ''ErrorDumpDir''
	, @value = @default_error_directory OUTPUT
;
INSERT INTO @AuditData VALUES (''Default ERROR Log Directory'', @default_error_directory);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	2900
	, 'Server TCP Port'
	, 
	'
DECLARE @tcpport_reg_path VARCHAR(200);
SET @tcpport_reg_path =
    ''SOFTWARE\Microsoft\Microsoft SQL Server''
    + ''\'' + @instance_subdirectory
    + ''\MSSQLServer\SuperSocketNetLib\TCP\IPAll''
;
DECLARE @TCPport VARCHAR(20);
EXEC xp_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = @tcpport_reg_path
    , @value_name = ''TcpPort''
    , @value = @TCPport OUTPUT
;
IF (@TCPport IS NULL)
BEGIN
	SET @tcpport_reg_path =
		''SOFTWARE\Microsoft\Microsoft SQL Server''
		+ ''\'' + @InstanceName
		+ ''\MSSQLServer\SuperSocketNetLib\TCP''
	;
	EXEC xp_regread
		@rootkey = ''HKEY_LOCAL_MACHINE''
		, @key = @tcpport_reg_path
		, @value_name = ''TcpPort''
		, @value = @TCPport OUTPUT
	;
END;
INSERT INTO @AuditData VALUES (''Server TCP Port'', @TCPport);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3000
	, 'SQL Server Command Line Arguments'
	, 
	'
DECLARE @CurArg INT;
DECLARE @LastArgRet VARCHAR(1000);
SET @CurArg = 0;
SET @LastArgRet = 1;
WHILE (@LastArgRet IS NOT NULL)
BEGIN
	DECLARE @CurKeyString VARCHAR(20);
	DECLARE @command_argument VARCHAR(1000);
	SET @CurKeyString = ''SQLArg'' + CAST(@CurArg AS VARCHAR(20));
	
	EXEC master.dbo.xp_instance_regread
		@rootkey = ''HKEY_LOCAL_MACHINE''
		, @key = ''Software\Microsoft\MSSQLServer\MSSQLServer\Parameters''
		, @value_name = @CurKeyString
		, @value = @command_argument OUTPUT
	;
	
	IF (@LastArgRet != @command_argument)
	BEGIN
		SET @LastArgRet = @command_argument;
		INSERT INTO @AuditData VALUES (
			''Server Command Line Arg#'' + CAST(@CurArg AS VARCHAR(20))
			, @command_argument
		);
	END;
	ELSE
	BEGIN
		SET @LastArgRet = NULL;
	END;
	SET @CurArg = @CurArg + 1;
END;
'
	);

----
----  Dump out all server roles
----  Current roles:
----    sysadmin
----    securityadmin
----    serveradmin
----    setupadmin
----    processadmin
----    diskadmin
----    dbcreator
----    bulkadmin
INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3100
	, 'Server Roles'
	, 
	'
DECLARE @ServerRoleDump TABLE (
	ServerRole sysname NOT NULL
	, MemberName sysname NULL
	, MemberSID VARBINARY(85) NULL
);

--
-- Server Administrative Users
INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''sysadmin''
;

DECLARE @sysadminUserList VARCHAR(max);
SELECT @sysadminUserList = STUFF(
             (SELECT '','' + MemberName
              FROM @ServerRoleDump
				ORDER BY MemberName
              FOR XML PATH (''''))
             , 1, 1, '''')
;
INSERT INTO @AuditData VALUES (''ServerRole:sysadmin'', @sysadminUserList);

DELETE FROM @ServerRoleDump;

INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''sysadmin''
;

--
-- Server Administrative Users
INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''securityadmin''
;

DECLARE @securityadminUserList VARCHAR(max);
SELECT @securityadminUserList = STUFF(
             (SELECT '','' + MemberName
              FROM @ServerRoleDump
				ORDER BY MemberName
              FOR XML PATH (''''))
             , 1, 1, '''')
;
INSERT INTO @AuditData VALUES (''ServerRole:securityadmin'', @securityadminUserList);

DELETE FROM @ServerRoleDump;

--
-- Server Administrative Users
INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''serveradmin''
;

DECLARE @serveradminUserList VARCHAR(max);
SELECT @serveradminUserList = STUFF(
             (SELECT '','' + MemberName
              FROM @ServerRoleDump
				ORDER BY MemberName
              FOR XML PATH (''''))
             , 1, 1, '''')
;
INSERT INTO @AuditData VALUES (''ServerRole:serveradmin'', @serveradminUserList);

DELETE FROM @ServerRoleDump;

--
-- Server Administrative Users
INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''setupadmin''
;

DECLARE @setupadminUserList VARCHAR(max);
SELECT @setupadminUserList = STUFF(
             (SELECT '','' + MemberName
              FROM @ServerRoleDump
				ORDER BY MemberName
              FOR XML PATH (''''))
             , 1, 1, '''')
;
INSERT INTO @AuditData VALUES (''ServerRole:setupadmin'', @setupadminUserList);

DELETE FROM @ServerRoleDump;

--
-- Server Administrative Users
INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''processadmin''
;

DECLARE @processadminUserList VARCHAR(max);
SELECT @processadminUserList = STUFF(
             (SELECT '','' + MemberName
              FROM @ServerRoleDump
				ORDER BY MemberName
              FOR XML PATH (''''))
             , 1, 1, '''')
;
INSERT INTO @AuditData VALUES (''ServerRole:processadmin'', @processadminUserList);

DELETE FROM @ServerRoleDump;

--
-- Server Administrative Users
INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''diskadmin''
;

DECLARE @diskadminUserList VARCHAR(max);
SELECT @diskadminUserList = STUFF(
             (SELECT '','' + MemberName
              FROM @ServerRoleDump
				ORDER BY MemberName
              FOR XML PATH (''''))
             , 1, 1, '''')
;
INSERT INTO @AuditData VALUES (''ServerRole:diskadmin'', @diskadminUserList);

DELETE FROM @ServerRoleDump;


--
-- Server Administrative Users
INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''dbcreator''
;

DECLARE @dbcreatorUserList VARCHAR(max);
SELECT @dbcreatorUserList = STUFF(
             (SELECT '','' + MemberName
              FROM @ServerRoleDump
				ORDER BY MemberName
              FOR XML PATH (''''))
             , 1, 1, '''')
;
INSERT INTO @AuditData VALUES (''ServerRole:dbcreator'', @dbcreatorUserList);

DELETE FROM @ServerRoleDump;

--
-- Server Administrative Users
INSERT INTO @ServerRoleDump
EXEC sp_helpsrvrolemember
	@srvrolename = ''bulkadmin''
;

DECLARE @bulkadminUserList VARCHAR(max);
SELECT @bulkadminUserList = STUFF(
             (SELECT '','' + MemberName
              FROM @ServerRoleDump
				ORDER BY MemberName
              FOR XML PATH (''''))
             , 1, 1, '''')
;
INSERT INTO @AuditData VALUES (''ServerRole:bulkadmin'', @bulkadminUserList);

DELETE FROM @ServerRoleDump;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3200
	, 'Authentication Mode'
	, 
	'
DECLARE @AuthenticationMode INT;
DECLARE @AuthenticationModeText VARCHAR(100);
EXEC master.dbo.xp_instance_regread
	@rootkey = ''HKEY_LOCAL_MACHINE''
	, @key = ''Software\Microsoft\MSSQLServer\MSSQLServer''
	, @value_name = ''LoginMode''
	, @value = @AuthenticationMode OUTPUT
;
SELECT @AuthenticationModeText =
		CASE WHEN @AuthenticationMode = 1
			THEN ''Windows Authentication''
		WHEN @AuthenticationMode = 2
			THEN ''Mixed''
		ELSE ''Unknown''
	END
;
INSERT INTO @AuditData VALUES (''Authentication Mode'', @AuthenticationModeText);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3300
	, 'Verify SA has a password'
	, 
	'
IF (@AuthenticationModeText = ''Mixed'')
BEGIN
	DECLARE @SAPasswordEmpty INT;
	DECLARE @SAPasswordLastMod DATETIME;
	
	SELECT @SAPasswordEmpty = PWDCOMPARE('''', password)
	FROM sys.syslogins
	WHERE NAME = ''sa'';

	SELECT @SAPasswordLastMod = CAST(LOGINPROPERTY(''sa'', ''PasswordLastSetTime'') AS VARCHAR(30));
	
	IF (@SAPasswordEmpty = 1)
	BEGIN
		INSERT INTO @AuditData VALUES (''SA has EMPTY password **'', ''yes'');
		INSERT INTO @AuditData VALUES (''SA Last Modified'', CONVERT(VARCHAR(20), @SAPasswordLastMod, 120));
	END;
	ELSE
	BEGIN
		INSERT INTO @AuditData VALUES (''SA has EMPTY password'', ''no'');
		INSERT INTO @AuditData VALUES (''SA Last Modified'', CONVERT(VARCHAR(20), @SAPasswordLastMod, 120));
	END;
END;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3400
	, 'Login Auditing Level'
	, 
	'
DECLARE @LoginAuditingLevel INT;
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\MSSQLServer''
    , @value_name = ''AuditLevel''
    , @value = @LoginAuditingLevel OUTPUT
;
INSERT INTO @AuditData
VALUES (
	''Login Audit Level''
	, CASE
	    WHEN (@LoginAuditingLevel = 0)
		THEN ''None''
	    WHEN (@LoginAuditingLevel = 1)
		THEN ''Successful Only''
	    WHEN (@LoginAuditingLevel = 2)
		THEN ''Failed Only''
	    WHEN (@LoginAuditingLevel = 3)
		THEN ''All Logins''
	    ELSE ''UNKNOWN LEVEL''
	 END
)
;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3500
	, 'Database Mail Configuration'
	, 
	'
DECLARE @IsDBMailEnabled VARCHAR(5);
SELECT @IsDBMailEnabled =
	CASE WHEN value = 1
		THEN ''yes''
	ELSE ''no''
	END
FROM sys.configurations
WHERE name = ''Database Mail XPs''
;
INSERT INTO @AuditData VALUES (''Database Mail Configured'', @IsDBMailEnabled);


IF (@IsDBMailEnabled = ''yes'')
BEGIN
	DECLARE @DBMailAccountCount INT;
	DECLARE @DBMailProfileCount INT;
	
	SELECT @DBMailAccountCount = COUNT(1)
	FROM msdb.dbo.sysmail_account;
	INSERT INTO @AuditData VALUES (''Database Mail Account Count'', CAST(@DBMailAccountCount AS VARCHAR(3)));
	
	
	SELECT @DBMailProfileCount = COUNT(1)
	FROM msdb.dbo.sysmail_profile;
	INSERT INTO @AuditData VALUES (''Database Mail Profile Count'', CAST(@DBMailProfileCount AS VARCHAR(3)));
	
	--
	-- Mail Accounts
	DECLARE @DBMailAccountList VARCHAR(max);
	SELECT @DBMailAccountList = STUFF(
				 (SELECT '','' + name
				  FROM msdb.dbo.sysmail_account
					ORDER BY name
				  FOR XML PATH (''''))
				 , 1, 1, '''')
	;
	INSERT INTO @AuditData VALUES (''Database Mail Accounts'', @DBMailAccountList);
	
	--
	-- Mail Profiles
	DECLARE @DBMailProfileList VARCHAR(max);
	SELECT @DBMailProfileList = STUFF(
				 (SELECT '','' + name
				  FROM msdb.dbo.sysmail_profile
					ORDER BY name
				  FOR XML PATH (''''))
				 , 1, 1, '''')
	;
	INSERT INTO @AuditData VALUES (''Database Mail Profiles'', @DBMailProfileList);
END;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3600
	, 'Log File Rotation Count'
	, 
	'
DECLARE @LogRotationCount INT;
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\MSSQLServer''
    , @value_name = ''NumErrorLogs''
    , @value = @LogRotationCount OUTPUT
;
INSERT INTO @AuditData VALUES (''Log Rotation Count'', CAST(@LogRotationCount AS VARCHAR(10)));
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3700
	, 'SQL Agent E-mail Profile'
	, 
	'
DECLARE @AgentEmailProfile VARCHAR(100);
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\SQLServerAgent''
    , @value_name = ''DatabaseMailProfile''
    , @value = @AgentEmailProfile OUTPUT
;
INSERT INTO @AuditData VALUES (''Agent E-mail Profile'', @AgentEmailProfile);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3800
	, 'Agent Error Log'
	, 
	'
DECLARE @AgentLogFileLoc VARCHAR(100);
EXEC master.dbo.xp_instance_regread
    @rootkey = ''HKEY_LOCAL_MACHINE''
    , @key = ''Software\Microsoft\MSSQLServer\SQLServerAgent''
    , @value_name = ''ErrorLogFile''
    , @value = @AgentLogFileLoc OUTPUT
;
INSERT INTO @AuditData VALUES (''Agent Error Log'', @AgentLogFileLoc);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	3900
	, 'SQL Agent Job Categories'
	, 
	'
INSERT INTO @AuditData
SELECT DISTINCT ''AgentCategory:'' + cat.NAME
	, cat.category_id
FROM msdb.dbo.sysjobs AS jobs
	JOIN msdb.dbo.syscategories AS cat
		ON jobs.category_id = cat.category_id
WHERE description NOT LIKE ''This job is owned by a report server process.%'' -- report server
	AND (
		cat.category_id < 10 -- replication
		OR cat.category_id > 20
		);
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	4000
	, 'Agent Jobs'
	, 
	'
INSERT INTO @AuditData
SELECT ''AgentJob:'' + name
	, CASE WHEN (enabled = 1)
				THEN ''enabled''
			ELSE ''DISabled''
		END
		+ 
		CASE WHEN (COUNT(scheds.schedule_id) > 0)
				THEN '':scheduled''
			ELSE '':UNscheduled''
		END AS job_status
FROM msdb.dbo.sysjobs AS jobs
	LEFT JOIN msdb.dbo.sysjobschedules AS scheds
		ON jobs.job_id = scheds.job_id
WHERE description NOT LIKE ''This job is owned by a report server process.%''  -- report server
	AND (category_id < 10		-- replication
		OR category_id > 20)
GROUP BY name
	, enabled
;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	4100
	, 'Drives and free space'
	, 
	'
DECLARE @Drives TABLE (
	drive_volume VARCHAR(50) NOT NULL
	, free_space INT NULL
)
;       
INSERT INTO @Drives
EXEC master.dbo.xp_fixeddrives
;
INSERT INTO @AuditData
SELECT ''Free Space GB Drive: '' + drive_volume
	, CAST(free_space/1024 AS VARCHAR(20))
FROM @Drives
;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	4200
	, 'Performance Statistics'
	, 
	'
INSERT INTO @AuditData
SELECT
    CASE
		WHEN (instance_name IS NOT NULL)
			THEN RTRIM(''Perf:SQL Statistics:'' + RTRIM(counter_name))
		ELSE ''UNKNOWN PERF?''
    END AS ikey
    , cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE ''%:SQL Statistics''
;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	4300
	, 'Performance Errors'
	, 
	'
INSERT INTO @AuditData
SELECT RTRIM(''Perf:SQL Errors:''
		+ RTRIM(counter_name)
		+ '':''
		+ RTRIM(instance_name))
	, cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE ''%:SQL Errors''
;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	4400
	, 'System Operator'
	, '
INSERT INTO @AuditData
SELECT ''SysOperator:'' + so.NAME
	, so.email_address
FROM msdb.dbo.sysoperators so
WHERE so.enabled = 1
ORDER BY so.NAME
;
'
	);

---- Are traces allowed by any person/group?
---- DB data/logs/tempdb on different drives
--    -- Check if free space too low?
---- make sure 'System Maintenance' script is set
---- Last full/log backup
INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	10000
	, 'Standard Configuration Data'
	, '
INSERT INTO @AuditData
SELECT ''Config:'' + name
	, CAST(value_in_use AS VARCHAR(1000))
FROM sys.configurations
ORDER BY name
;
'
	);

INSERT INTO auditQueries (
	queryOrder
	, queryName
	, query
	)
VALUES (
	999999999
	, 'AuditTable Data Select'
	, '
SELECT @ServerName AS ''ServerName''
	, ikey
	, ivalue
	, CAST(GETDATE() AS DATETIME)
FROM @AuditData
ORDER BY ikey
;
'
	);

-- SELECT *
-- FROM auditQueries;

--DECLARE @ServerScript NVARCHAR(max);

--SELECT @ServerScript = STUFF((
--			(
--				SELECT '' + query
--				FROM auditQueries
--				ORDER BY queryOrder
--				FOR XML PATH('')
--					, root('SomeRandomString')
--					, type
--				).value('/SomeRandomString[1]', 'NVARCHAR(max)')
--			), 1, 1, '');

----SELECT DATALENGTH(@ServerScript);
----SELECT @ServerScript;

--EXECUTE sp_executesql
--	@stmt = @ServerScript;

GO
